package com.feng.util;

import java.io.File;
import java.util.Date;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;

import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.NodeList;

import com.jfinal.core.JFinal;
import com.jfinal.plugin.activerecord.ActiveRecordPlugin;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.DbPro;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.c3p0.C3p0Plugin;


public class UpdateDB {

	public static void update(C3p0Plugin c3p0Plugin) {
		
		// 配置ActiveRecord插件
		ActiveRecordPlugin arp0 = new ActiveRecordPlugin("updateDataBase", c3p0Plugin);
		arp0.setShowSql(true);
		arp0.start();
		
		String path = JFinal.me().getServletContext().getRealPath("/");
		
		checkDataBase(path);
		
		excuteUpdate(path);
		
	    arp0.stop();
	}
	
	/**
     * 得到data对象
     * @param id server的ID
     * @param dbName 数据库名称
     */
	public static DbPro getDataConfig(){
		return Db.use("updateDataBase");
	}
	
	public static void checkDataBase(String path) {
		System.out.println("检测数据库......");
		
		String sql = "SELECT `TABLE_NAME` FROM `INFORMATION_SCHEMA`.`TABLES` " +
					 "WHERE " +
						"`TABLE_SCHEMA` = 'company' AND " +
						"`TABLE_NAME` = 'users'";
		
		Record sys = getDataConfig().findFirst(sql);
		if (sys != null) {
			return;
		}
		System.out.println("检测到您是新数据库，需创建新表，请耐心等待......");
		
		try {
			
			DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();   
		    DocumentBuilder builder = factory.newDocumentBuilder();  
		    Document doc = builder.parse(new File(path + "/WEB-INF/classes/com/sql/feng.xml"));   
		    Element root = doc.getDocumentElement();    
		    NodeList list = root.getElementsByTagName("batch");   
		    
		    for (int i=0; i < list.getLength() ; i++) {  
		    	
		    	Element element = (Element)list.item(i);  
		    	
			    NodeList nodes= element.getElementsByTagName("sql");
			    for (int j = 0; j < nodes.getLength(); j++) {
			    	Element ment = (Element)list.item(i); 
			    	
			    	getDataConfig().update(ment.getElementsByTagName("sql").item(j).getTextContent());
				}
		    }
		} catch (Exception e) {
			System.out.println("解析数据库文件失败");
			e.printStackTrace();
		}
		
		System.out.println("表创建完成,检测数据库更新......");
		
	}
	
	public static void excuteUpdate(String path) {
		System.out.println("开始更新数据库......");
		
		int maxVersion = 0,version = 0;
		Record sys = getDataConfig().findFirst("select id,ifnull(max(version),0) as version from sys_database group by id");
		 
		if (sys != null) {
			maxVersion = version = (int)(long)sys.getLong("version");
		}else {
			String sql = "insert into sys_database values(1, 'admin', now(), 0, now())";
			Db.update(sql);
		}
		 
		try {
			DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();   
		    DocumentBuilder builder = factory.newDocumentBuilder();  
		    Document doc = builder.parse(new File(path + "/WEB-INF/classes/com/sql/execute.xml"));   
		    Element root = doc.getDocumentElement();    
		    NodeList list = root.getElementsByTagName("batch");   
		    
		    for (int i=0; i < list.getLength() ; i++) {  
		    	
		    	Element element = (Element)list.item(i);  
		    	int ver = Integer.parseInt(element.getAttribute("version"));
		    	
		    	if(ver > version){
		    		maxVersion = ver;
		    		
				    NodeList nodes= element.getElementsByTagName("sql");
				    for (int j = 0; j < nodes.getLength(); j++) {
				    	Element ment = (Element)list.item(i); 
				    	
				    	String sql=ment.getElementsByTagName("sql").item(j).getTextContent();
				    	
				    	getDataConfig().update(sql);
					}
		    	}
		    }
		} catch (Exception e) {
			System.out.println("解析数据库文件失败");
			e.printStackTrace();
		}
		
	    if (maxVersion > version) {
	    	if (sys != null) {
	    		String sql = "update sys_database set version=?,last_update=? where id=?";
		    	getDataConfig().update(sql, maxVersion, new Date(), sys.get("id"));
	    	}else {
	    		String sql = "insert into sys_database(create_time,version,last_update) values(?,?,?)";
		    	getDataConfig().update(sql, new Date(), maxVersion, new Date());
	    	}
	    }
	    System.out.println("数据库更新完成......");
	}
}
